﻿
# DevScope BI PowerShell Samples
# 
# http://devscopebisamples.codeplex.com/
# http://www.devscope.net


Set-Location (Split-Path $MyInvocation.MyCommand.Definition –Parent)

function Invoke-OLEDBCommand{
	[CmdletBinding()]
	param(		
		[string] $connectionString=$conn,
		[Parameter(Mandatory=$true)] [string] $sql,		
		[hashtable] $parameters = $null,
		[int] $commandTimeout = 300
		)
		
	try
	{
		if ($connectionString -eq $null)
		{
			write-host "Using default connection..."
			$connectionString=$conn
		}

		$conn = New-Object System.Data.OleDb.OleDbConnection($connectionString)
	   	
		Write-Verbose ("Opening Connection to: '{0}'" -f $connectionString)
	
		write-host "Running query..."	
		$conn.Open()
		
	   	$cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $conn)
		
	   	$cmd.CommandTimeout = $commandTimeout			
		
		if ($parameters -ne $null)
		{
			$parameters.GetEnumerator() |% {
				$cmd.Parameters.AddWithValue($_.Name, $_.Value)	| Out-Null
			}			
		}				
		
		Write-Host ("Executing SQL: '{0}'" -f $sql)
		
		$da = New-Object System.Data.OleDb.OleDbDataAdapter($cmd)
		
	    $dt = New-Object System.Data.DataTable
		
	    $da.Fill($dt) | Out-Null   		
		
		$da.Dispose()
		
		$cmd.Dispose()		
		
		@("HasErrors", "ItemArray", "RowError", "RowState", "Table") |% {
			if ($dt.Columns.Contains($_))
			{
				$dt.Columns.Remove($_)
			}
		}
		
		write-host "Row count: $($dt.Rows.Count)"

		Write-Output $dt
		
		#Write-Output $dt | Select-Object * -Exclude HasErrors, ItemArray, RowError, RowState, Table	
		
	}
	finally
	{
		if ($conn -ne $null)
		{
			$conn.Close()
			$conn.Dispose()
			$conn = $null
		}
	}	
}

function Out-Email {	
	[CmdletBinding(SupportsShouldProcess = $true)]
	param(
		[Parameter(ValueFromPipeline=$true)] $inputObject = @(),
		[Parameter(Mandatory=$true)] [string[]] $to ,
		[string] $from,
	    [string] $subject = "Out-Email",	    
		[string] $message = "",	
		[string] $smtpServer,
		[int] $smtpPort = -1,
		[System.Net.NetworkCredential]$smtpCredentials,
		[switch] $smtpEnableSSL = $false,
		[scriptblock] $sendConditionExpression
		)
	begin {
		$data = @()
	}
	process {
		$data += $inputObject		
	}
	end {					
		try
		{
			write-host "Creating email..."

			# Only send the mail if the condition is true
			
			$conditionResult = $true;
									
			if ($sendConditionExpression -ne $null)
			{					
				$conditionResult = $false							
				
				$foundObjs = $data |? $sendConditionExpression
				
				if ($foundObjs -ne $null -and $foundObjs.Count -ne 0)
				{
					$conditionResult = $true					
				}					
			}
			
			if ($conditionResult)
			{
				# Send the Email
				
				$html = "<style>
							body { font-family:Tahoma; font-size:10pt; }	
							.table { border-collapse:collapse; padding: 0px; margin: 0px }	
							.table th { color:white; background-color:darkgray; }
							.table td, th { border:1px solid gray; padding:0px 5px 0px;}
							.highlight{
								background-color: #f2dede;
							}
						</style>"				
			
				$html += $data | ConvertTo-HtmlTable

				$html += "<br> By <a href=""www.devscope.net"">www.devscope.net</a> bi samples at <a href=""http://devscopebisamples.codeplex.com/"">http://devscopebisamples.codeplex.com/</a>"
				
				$toStr = ([string]::Join(",", $to))
				
				Write-Output $html
				
				if (-not $WhatIfPreference.IsPresent)
				{
					Write-Host ("Sending email to: {0}" -f $toStr)
					
					$smtpArgs = @($smtpServer)					
					
					if ($smtpPort -ne -1)
					{
						$smtpArgs += $smtpPort
					}
					
					$smtpClient = New-Object Net.Mail.SmtpClient -ArgumentList $smtpArgs 		
					$smtpClient.EnableSSL = $smtpEnableSSL 
					$smtpClient.Credentials = $smtpCredentials
					
					$emailMessage = New-Object System.Net.Mail.MailMessage
					$emailMessage.From = $from
					
					$to |% {
						$emailMessage.To.Add($_)
					}		
					
					$emailMessage.Subject = $subject
					$emailMessage.Body = $html	
					$emailMessage.IsBodyHtml = $true			

					$smtpClient.Send($emailMessage)																								
				}
				else
				{
					Write-Host "[WhatIf Mode]"
				}												
				
				Write-Host ("Email sent to: {0}" -f $toStr)

				if ($html-ne $null) 	{
					$html | Set-Content -Path ".\out-mail.html"
		
					start ".\out-mail.html"
					}				
				
			}
			else
			{
				Write-Host "Send mail condition failed"
			}
		}
		catch
		{
			Write-Error -Message $_.Exception.ToString() -Exception $_.Exception
		}
	}
}

function ConvertTo-HtmlTable {
[CmdletBinding(SupportsShouldProcess = $true)]
	param(
		[Parameter(ValueFromPipeline=$true)] $inputObject = @()	
		)
	begin {

		write-host "Formatting table..."
		
		$html = New-Object Text.StringBuilder("<table class = 'table'>")
		
		$sample = $inputObject | select -First 1		
		
		if ($sample -ne $null)
		{					
			$dataTableSysColumns = @("RowError", "RowState", "Table", "ItemArray", "HasErrors","Alert")			
			$sampleProperties = $sample.PSObject.Properties | select -ExpandProperty Name -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors;
			$samplePropertiesToPrint = $sampleProperties |? {-not (($_ -like "sys*") -or ($dataTableSysColumns -contains $_))}
			
			$html.Append("<tr>") | Out-Null
			
			$samplePropertiesToPrint |% {
				
				$html.AppendFormat("<th>{0}</th>", $_) | Out-Null
				
				
			}
			
			$html.Append("</tr>") | Out-Null
			
			$hasSysRowCssClass = $sampleProperties -contains "SysRowCssClass"
		}
	}
	process {
		
		$pipeObj = $_		
		
		if ($pipeObj -ne $null -and $samplePropertiesToPrint -ne $null)
		{			
			$rowCssClass = $null
			
			if ($hasSysRowCssClass -and (-not [string]::IsNullOrEmpty($pipeObj.SysRowCssClass)))
			{
				$rowCssClass = " class = '{0}'" -f $pipeObj.SysRowCssClass
			}
			
			$html.Append("<tr{0}>" -f $rowCssClass) | Out-Null
						
			$samplePropertiesToPrint |% {
			try{
				$a=0
				if ($pipeObj.($_) -ne $null -and 
					([double]::TryParse( $pipeObj.($_) , [ref]$a )))

					{$html.Append(("<td>{0:#,0.####}</td>" -f $pipeObj.($_))) | Out-Null}
				else
					{$html.Append(("<td>{0}</td>" -f $pipeObj.($_))) | Out-Null}				
				}
				catch{
				$_
				}
			}			
			
			$html.Append("</tr>") | Out-Null
		}				
	}
	end {	
		$html.Append("</table>") | Out-Null
		
		Write-Output $html.ToString()
	}
}


function Set-Outliers
{
      [CmdletBinding(SupportsShouldProcess = $true)]
      param(
            [Parameter(Mandatory=$true)] [System.Data.DataTable] $dataTable,
            [Parameter(Mandatory=$true)] [string] $connectionString
            )

	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient") | Out-Null
      
	write-host "Detecting outliers..."            

      $trainMiningDataTable = $dataTable.Copy()

      # Remove unwanted columns

      @("Date", "Id", "Text", "Detail", "Message") |% {          
            if ($trainMiningDataTable.Columns.Contains($_))
            {
                  $trainMiningDataTable.Columns.Remove($_)
            }
      }

      # Add RowIndex to the DataTable

      $rowIndexCol = $trainMiningDataTable.Columns.Add("_RowIndex", [long])
      $rowIndexCol.SetOrdinal(0)

      $i = 0

      $trainMiningDataTable.Rows |% {
            
            $_[$rowIndexCol] = $i
            
            $i++
      }

      # Computes the datamining required columns

      $dataMiningColumns = $trainMiningDataTable.Columns |% {
            
            $column = $_
            
            $properties = @{}
            
            $properties += @{"Name" = $_.ColumnName}
            
            $dataMiningType = [Microsoft.AnalysisServices.AdomdClient.MiningColumnType]::Text
                  
            if (@([int16],[int],[long],[UInt16],[uint32],[uint64]) -contains $_.DataType)
          {
              $dataMiningType = [Microsoft.AnalysisServices.AdomdClient.MiningColumnType]::Long;
          }
          else 
            {
                  if ($_.DataType -eq [bool])
                {
                    $dataMiningType = [Microsoft.AnalysisServices.AdomdClient.MiningColumnType]::Boolean;
                }
                else
                  {
                        if ($_.DataType -eq [datetime])
                      {
                          $dataMiningType = [Microsoft.AnalysisServices.AdomdClient.MiningColumnType]::Date;
                      }
                      else
                        {
                             if ($_.DataType -eq [decimal])
                            {                
                                $dataMiningType = [Microsoft.AnalysisServices.AdomdClient.MiningColumnType]::Double;
                            }
                            else
                             {
                                   if (($_.DataType -eq [double]) -or ($_.DataType -eq [float]))
                                 {                
                                     $dataMiningType = [Microsoft.AnalysisServices.AdomdClient.MiningColumnType]::Double;
                                 }
                                 else
                                 {                
                                     $dataMiningType = [Microsoft.AnalysisServices.AdomdClient.MiningColumnType]::Text;
                                 }
                             }
                        }
                  }
            }
                        
            $properties += @{"DataMiningType" = $dataMiningType}
            
            if ($column.ColumnName -eq "_RowIndex")
            {
                  $dataMiningContentType = "KEY"
            }
            else
            {
                  if ($dataMiningType -eq [Microsoft.AnalysisServices.AdomdClient.MiningColumnType]::Long -or $dataMiningType -eq [Microsoft.AnalysisServices.AdomdClient.MiningColumnType]::Double)
                  {
                        $dataMiningContentType = "Continuous"
                  }
                  else
                  {
                        $dataMiningContentType = "Discrete"
                  }
            }

            $properties += @{"DataMiningContentType" = $dataMiningContentType}
            
            New-Object PSObject -Property $properties
      }

      $miningStructureTableName = "Table_{0}" -f [Guid]::NewGuid().ToString("N")
      $miningStructureSessionModelName = "{0}_SessionModel" -f $miningStructure

      $createMiningStructureCommand = "CREATE SESSION MINING STRUCTURE [$miningStructureTableName] ( 
      {0}
      )" -f [string]::Join(", ", ($dataMiningColumns |% { "[{0}] {1} {2}" -f $_.Name, $_.DataMiningType.ToString(), $_.DataMiningContentType }))

      $createMiningSessionModelCommand = "ALTER MINING STRUCTURE [$miningStructureTableName] ADD SESSION MINING MODEL [$miningStructureSessionModelName](
      {0}
      ) USING Microsoft_Clustering(CLUSTER_COUNT=0) WITH DRILLTHROUGH" -f [string]::Join(", ", ($dataMiningColumns |% { "[{0}] PREDICT" -f $_.Name }))

      $trainMiningStructureCommand = "INSERT INTO MINING STRUCTURE [$miningStructureTableName] (
            {0}) @ParamTable" -f [string]::Join(", ", ($dataMiningColumns |% { "[{0}]" -f $_.Name }))

      

      try
      {
            $conn = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection

            $conn.ConnectionString = $connectionString
            
            Write-Verbose ("Opening SSAS connection '{0}'" -f $conn.ConnectionString)
            
            $conn.Open()

            $cmd = $conn.CreateCommand()

            $cmd.CommandText = $createMiningStructureCommand
            
            Write-Verbose ("Executing DMX: '{0}'" -f $cmd.CommandText)

	    write-host "Create mining structure..."            

            $cmd.ExecuteNonQuery() | Out-Null

            $cmd.CommandText = $createMiningSessionModelCommand
            
            Write-Verbose ("Executing DMX: '{0}'" -f $cmd.CommandText)

            write-host "Create mining session model..."                                    

            $cmd.ExecuteNonQuery() | Out-Null

            $cmd.CommandText = $trainMiningStructureCommand
            
            Write-Verbose ("Executing DMX: '{0}'" -f $cmd.CommandText)

            $paramTable = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdParameter
            $paramTable.ParameterName = "ParamTable"
            $paramTable.Direction = [System.Data.ParameterDirection]::Input
            $paramTable.Value = $trainMiningDataTable

            $cmd.Parameters.Add($paramTable) | Out-Null

            write-host "Training..."                                    

            $cmd.ExecuteNonQuery() | Out-Null

            $cmd.CommandText = "SELECT T.[_RowIndex], PredictCaseLikelihood() as SysDataMiningStatus FROM [$miningStructureSessionModelName] NATURAL PREDICTION JOIN @ParamTable as T"

            Write-Verbose ("Executing DMX: '{0}'" -f $cmd.CommandText)

	    write-host "Running prediction stats..."                                                

            $reader = $cmd.ExecuteReader()

            $collection = @()

            # Read the result 

            $resultDataTable = $dataTable.Copy()
            
            $dataMiningStatusCol = $resultDataTable.Columns.Add("OutlierProbability", [double])

            $rowIndexOrdinal = $reader.GetOrdinal("_RowIndex")

            $sysDataMiningStatusOrdinal = $reader.GetOrdinal("SysDataMiningStatus")

            while($reader.Read()) 
            { 
                  $properties = @{}
                  
                  $rowIndex = $reader.GetValue($rowIndexOrdinal)
                  
                  $status = $reader.GetValue($sysDataMiningStatusOrdinal)
                  
                  $resultDataTable.Rows[$rowIndex][$dataMiningStatusCol] = 1-[math]::round($status,4)
		
		  if ($resultDataTable.Rows[$rowIndex][$dataMiningStatusCol] -eq 0) {
			$resultDataTable.Rows[$rowIndex][$dataMiningStatusCol]=2
			}
            } 

	    write-host "Mining done!"                                    
      }
      finally
      {
            if ($conn -ne $null)
            {
                  $conn.Close()
                  $conn.Dispose()
            }
      }

      Write-Output $resultDataTable
}


Export-ModuleMember -Function @("Out-Email","Invoke-OLEDBCommand", "ConvertTo-HtmlTable", "Set-Outliers")
